<?php
/**
 * Created by PhpStorm.
 * User: a
 * Date: 2016/6/7
 * Time: 17:32
 */
namespace Admin\Model;
use Think\Model;

class BorrowBuinessinfoModel extends Model 
{
	protected $trueTableName = 'bypal_borrow_buinessinfo';


	/**
	 * 投资管理详情页数据
	 */
	public function get_invest_detail($loanGrade,$loanPeriod) 
	{
		$where = " where t2.borrow_investId=0 ";

		if($loanGrade) 
		{
			$where = $where." and t3.cl_name like '%$loanGrade%' ";
		}
		if($loanPeriod) 
		{
			$where = $where." and t2.borrow_loanperiod=".$loanPeriod;
		}

		$select_sql = "select t2.borrow_bid,t2.borrow_title,t2.borrow_money,t2.borrow_rate,t2.borrow_loanperiod,t2.borrow_investId,t3.cl_name,t5.principal 
				from (select binfo_id,binfo_borrowid from bypal_borrow_buinessinfo where binfo_state=3) t1 
				left join bypal_borrow_info t2 on t1.binfo_borrowid=t2.borrow_id 
				left join bypal_cust_level t3 on t2.borrow_level=t3.cl_id 
				left join bypal_facilitator_info t4 on t2.borrow_busid=t4.id 
				left join (select sum(principal) principal,binfo_id from bypal_repay_detail where repay_date=0 group by binfo_id) t5 on t1.binfo_borrowid=t5.binfo_id".$where;
		$Model = new \Think\Model();
		$values = $Model->query($select_sql);

		foreach ($values as $key => $value) 
		{
			$values[$key]['principal'] = round($value['principal']);
		}

		return $values;
	}

    public function borrow_business($searchArray,$page,$rows){
        $sql="select t2.borrow_id,t6.name,t5.fac_simple_name,t2.borrow_money,t2.borrow_rate,case when t7.delay_time=0 then
        t2.borrow_loanperiod else concat(t2.borrow_loanperiod,'+',t7.delay_time ) end borrow_loanperiod,t3.cust_name,FROM_UNIXTIME(t1.binfo_opendate,'%Y-%m-%d') binfo_opendate,
        FROM_UNIXTIME(t8.end_date,'%Y-%m-%d') end_date,case when borrow_investId=0 then '否' else '是' end borrow_invest,t4.bsta_name,t1.binfo_state,FROM_UNIXTIME(t1.buinfo_repaydown,'%Y-%m-%d') buinfo_repaydown,
        FROM_UNIXTIME(t2.borrow_passtime,'%Y-%m-%d') borrow_passtime
        from bypal_borrow_buinessinfo t1 left join bypal_borrow_info t2 on t1.binfo_borrowid=t2.borrow_id
        left join bypal_cust_info t3 on t2.borrow_custid=t3.cust_id
        left join bypal_borrow_status t4 on t1.binfo_state=t4.bsta_id
        left join bypal_facilitator_info t5 on t2.borrow_busid = t5.id
        left join bypal_facilitator_itype t6 on t5.fac_separatetype =t6.trade_number
        left join bypal_facilitator_trade t7 on t1.binfo_borrowid=t7.trade_contractnumber
        left join (select binfo_id,min(end_date) end_date from bypal_repay_detail where repay_date=0 group by binfo_id) t8 on t1.binfo_borrowid=t8.binfo_id
        where 1=1 ";

        $count_sql="select count(t1.binfo_borrowid) count
        from bypal_borrow_buinessinfo t1 left join bypal_borrow_info t2 on t1.binfo_borrowid=t2.borrow_id
        left join bypal_cust_info t3 on t2.borrow_custid=t3.cust_id
        left join bypal_facilitator_info t5 on t2.borrow_busid = t5.id
        left join bypal_facilitator_trade t7 on t1.binfo_borrowid=t7.trade_contractnumber
        where 1=1 ";

        $sum_sql="select IFNULL(ROUND(sum(t6.principal),2),0) principal,IFNULL(ROUND(sum(t6.interest),2),0) interest,IFNULL(ROUND(sum(t8.principal),2),0) un_princupal,
    IFNULL(ROUND(sum(t8.interest),2),0) un_interest from bypal_borrow_buinessinfo  t1
    left join bypal_borrow_info t2 on t1.binfo_borrowid=t2.borrow_id
    left join bypal_cust_info t3 on t2.borrow_custid=t3.cust_id
    left join bypal_borrow_status t4 on t1.binfo_state=t4.bsta_id
    left join bypal_facilitator_info t5 on t2.borrow_busid = t5.id
    left join (select sum(actual_pricipal) principal,sum(actual_interest) interest,binfo_id from bypal_repay_detail where repay_date!=0 group by binfo_id) t6 on t1.binfo_borrowid=t6.binfo_id
    left join bypal_facilitator_trade t7 on t1.binfo_borrowid=t7.trade_contractnumber
		left join (select sum(principal) principal,sum(interest) interest,binfo_id from bypal_repay_detail where repay_date=0 group by binfo_id) t8 on t1.binfo_borrowid=t8.binfo_id
		where 1=1 ";

        $where="";
        if($searchArray['select_borrowNum']!=''){
            $where.=" and t1.binfo_borrowid='".$searchArray['select_borrowNum']."'";
        }
        if($searchArray['select_custName']!=''){
            $where.=" and t3.cust_name like '%".$searchArray['select_custName']."%'";
        }
        if($searchArray['select_facName']!=''){
            $where.=" and t5.fac_simple_name like '%".$searchArray['fac_simple_name']."%'";
        }
        if($searchArray['select_delayStatus']!=''){
            $select_delayStatus=$searchArray['select_delayStatus'];
            if($select_delayStatus==1) {
                $where .= " and t7.delay_time !=0 ";
            }else if($select_delayStatus==2){
                $where .= " and t7.delay_time =0 ";
            }
        }
        if($searchArray['select_start_time']!=''&&$searchArray['select_end_time']){
            $select_start_time=strtotime($searchArray['select_start_time']);
            $select_end_time=strtotime($searchArray['select_end_time']." 23:59:59");
            $where.=" and t2.borrow_passtime between '$select_start_time' and '$select_end_time'";
        }
        if($searchArray['select_borrowStatus']!=''){
            $select_borrowStatus=$searchArray['select_borrowStatus'];
            if($select_borrowStatus==10){
                $where.=" and t1.binfo_state in (3,4)";
            }else{
                $where.=" and t1.binfo_state =$select_borrowStatus";
            }
        }
        $sql.=$where;
        $count_sql.=$where;
        $sum_sql.=$where;
        $sql.=" order by t2.borrow_passtime desc";
        $maxPage=($page-1)*$rows;
        $limit=" limit $maxPage,$rows";
        $sql.=$limit;
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        $count_values=$Model->query($count_sql);
        $sum_values=$Model->query($sum_sql);


        $array_sum = array();
        $array_sum[0]['borrow_id'] = '总本金:';
        $array_sum[0]['name'] = $sum_values[0]['principal']+$sum_values[0]['un_princupal'] . ' 元';
        $array_sum[0]['fac_simple_name'] = '总利息:';
        $array_sum[0]['borrow_money'] = $sum_values[0]['interest']+$sum_values[0]['un_interest'] . ' 元';
        $array_sum[0]['borrow_rate'] = '总额:';
        $array_sum[0]['borrow_loanperiod'] = $sum_values[0]['principal'] + $sum_values[0]['interest']+$sum_values[0]['un_princupal']+$sum_values[0]['un_interest'] . ' 元';
        $array_sum[1]['borrow_id'] = '存量本金:';
        $array_sum[1]['name'] = $sum_values[0]['un_princupal'] . ' 元';
        $array_sum[1]['fac_simple_name'] = '存量利息:';
        $array_sum[1]['borrow_money'] = $sum_values[0]['un_interest'] . ' 元';
        $array_sum[1]['borrow_rate'] = '存量总额:';
        $array_sum[1]['borrow_loanperiod'] = $sum_values[0]['un_princupal'] + $sum_values[0]['un_interest'] . ' 元';
        $result['total']=$count_values[0]['count'];
        $result['rows']=$values;
        $result['footer']=$array_sum;
        return $result;
    }

    public function forecast_repayment($day,$order,$page,$rows){
        $sql="select t1.binfo_borrowid,t5.fac_simple_name,t7.cust_name,t4.fund_balance,t2.period_number,FROM_UNIXTIME(t2.start_date,'%Y-%m-%d') start_date,
        FROM_UNIXTIME(t2.end_date,'%Y-%m-%d') end_date,t2.principal,t2.interest,t2.poundage,
        case when t2.repay_date=0 then '未还款' else FROM_UNIXTIME(t2.repay_date,'%Y-%m-%d') end repay_date,t6.remain_time from bypal_borrow_buinessinfo t1
        left JOIN bypal_repay_detail t2 ON t1.binfo_borrowid = t2.binfo_id
        left join bypal_borrow_info t3 on t1.binfo_borrowid=t3.borrow_id
        left join bypal_balance_invest t4 on t3.borrow_custid=t4.cust_id
        left join bypal_facilitator_info t5 on t3.borrow_busid=t5.id
        left join (select count(repay_detail_id) remain_time,binfo_id from bypal_repay_detail where repay_date=0 group by binfo_id) t6
        on t1.binfo_borrowid=t6.binfo_id
        left join bypal_cust_info t7 on t3.borrow_custid=t7.cust_id";

        $count_sql="select count(t2.repay_detail_id) count from bypal_borrow_buinessinfo t1 left join bypal_repay_detail t2 on t1.binfo_borrowid = t2.binfo_id ";

        $sum_sql="select round(sum(t2.principal),2) principal,round(sum(t2.interest),2) interest from bypal_repay_detail t2 ";

        $start_date=strtotime(date('Y-m-d'),time());
        $end_date=$start_date+86400*$day;

        $where=" where t2.end_date>=$start_date and t2.end_date<=$end_date ";
        $sql.=$where." and t1.binfo_state=3 ".$order;
        $count_sql.=$where." and t1.binfo_state=3 ";
        $sum_sql.=$where." and t2.repay_date=0 ";

        $maxPage=($page-1)*$rows;
        $limit=" limit $maxPage,$rows";
        $sql.=$limit;
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        $count_values=$Model->query($count_sql);
        $sum_values=$Model->query($sum_sql);

        $array_sum = array();
        $array_sum[0]['fac_simple_name'] = '未还本金:';
        $array_sum[0]['cust_name'] = $sum_values[0]['principal'] . ' 元';
        $array_sum[0]['period_number'] = '未还利息:';
        $array_sum[0]['start_date'] = $sum_values[0]['interest']. ' 元';
        $array_sum[0]['principal'] = '未还总额:';
        $array_sum[0]['interest'] = $sum_values[0]['principal'] + $sum_values[0]['interest'] . ' 元';
        $result['total']=$count_values[0]['count'];
        $result['rows']=$values;
        $result['footer']=$array_sum;
        return $result;
    }


    /**
     * 分期借款业务详情， 订单信息 未还部分_已还_总还
     * @param $borrow_id
     */
    public function borrow_info_detail_repaid_info($borrow_id){
        $sql="select 
t2.sum_period_recovery_repay,

t3.principal as month_repay_principal_remain,
t3.interest as month_repay_interest_remain,
(t3.interest+t3.principal) as month_repay_money_remain,

t2.sum_fine_money_repay as sum_fine_money_repay,
(t1.sum_pricipal+t1.sum_interest+t1.sum_poundage) as sum_money_should_repay,
(t2.sum_actual_pricipal_repay-t2.sum_principal_repay) as sum_principal_advance_repay,
t2.sum_actual_pricipal_repay,
t2.sum_principal_repay,


t2.sum_poundage_repay,
t1.sum_poundage as sum_poundage,
(t1.sum_poundage-t2.sum_poundage_repay) as sum_poundage_not_repay,
t1.sum_demurrage ,
t2.sum_demurrage_repay,
(t1.sum_demurrage - t2.sum_demurrage_repay) as sum_demurrage_not_repay,

(t2.sum_period_recovery_repay-t2.sum_fine_money_repay-t2.sum_demurrage_repay-t2.sum_actual_pricipal_repay-t2.sum_poundage_repay) AS sum_interest_repay,
t3.sum_interest as sum_interest_not_repay,
t1.sum_pricipal as sum_pricipal_moneyy,
(t1.sum_pricipal-t2.sum_actual_pricipal_repay) as sum_pricipal_remain,


t1.binfo_id
from 
(select
ROUND( sum(principal),2) as sum_pricipal,
ROUND( SUM(period_recovery),2)as sum_period_recovery,
ROUND( sum(interest),2) as sum_interest,
ROUND( sum(poundage),2) as sum_poundage,
ROUND( sum(demurrage),2) as sum_demurrage,
binfo_id 
from bypal_repay_detail group by binfo_id) t1 
left join 
(select 
ROUND( SUM(period_recovery),2) as sum_period_recovery_repay,
ROUND( sum(actual_pricipal),2) as sum_actual_pricipal_repay,
ROUND( SUM(principal),2) as sum_principal_repay,
ROUND( SUM(demurrage),2) as sum_demurrage_repay ,
ROUND( SUM(poundage),2) as sum_poundage_repay,
ROUND( SUM(fine_money),2) as sum_fine_money_repay,
binfo_id 
from bypal_repay_detail where repay_date!=1  GROUP BY binfo_id) t2
on t1.binfo_id=t2.binfo_id 

left join 
(SELECT
ROUND(principal,2) as principal,
ROUND(interest,2) as interest,
ROUND(sum(interest),2) as sum_interest,

binfo_id
FROM  bypal_repay_detail where repay_date=1  GROUP BY binfo_id) t3
on t1.binfo_id=t3.binfo_id 

where t1.binfo_id=$borrow_id

";

        $Model = new \Think\Model();
        return $Model->query($sql);

    }
    /**
     * 分期借款业务详情，全部订单信息 列表信息
     * @param $borrow_id
     * @return mixed
     */
    public function borrow_info_detail_table($borrow_id){
        $sql="SELECT repay_detail_id,period_number,period_recovery,
IFNULL(principal,0) as principal,
IFNULL(actual_pricipal,0 ) as actual_pricipal ,IFNULL(interest,0) as interest ,
IFNULL(actual_interest,0)as actual_interest ,IFNULL(fine_money,0) as fine_money,
IFNULL(poundage,0)as poundage ,
 case when isoverdue=0 THEN '未逾期'  ELSE '逾期'   END as isoverdue ,
 case when repay_date=0 THEN '未还' WHEN repay_date=1 then '未还' ELSE  FROM_UNIXTIME(repay_date,'%Y-%m-%d')  END as repay_date ,
IFNULL(demurrage,0) as demurrage
  FROM bypal_repay_detail WHERE binfo_id=$borrow_id  order by period_number ASC 
 ";
        $Model = new \Think\Model();
        return  $Model->query($sql);
    }
    /**
     * 分期借款业务详情，  订单信息  用户账户信息 商户信息
     * @param $borrow_id
     * @return mixed
     */
    public function borrow_info_fac_detail($borrow_id){
        $sql="SELECT t1.borrow_id,t1.borrow_title, t1.borrow_busid, t1.borrow_custid ,t1.borrow_money,t1.borrow_rate,t1.borrow_loanperiod
,t1.borrow_extramoney,t1.borrow_stateid,case when t1.borrow_unworktime=0 
THEN \"未取消\" ELSE  FROM_UNIXTIME(t1.borrow_unworktime,'%Y-%m-%d')  END as state,
t2.fac_name,t2.fac_showaddress,t2.fac_contact,t2.fac_contactphone,
t4.trade_perofpay,t4.delay_time,
50000-sum(t5.principal)as mostNum,
t6.man_type,
t7.ad_level,
t8.`name`,
t9.cust_name,t9.cust_phone,
t10.lt_value,
t11.repay_name,
t12.fund_balance
FROM bypal_borrow_info t1 
left JOIN bypal_facilitator_info t2 ON t1.borrow_busid=t2.id
LEFT JOIN bypal_borrow_buinessinfo t3 ON t1.borrow_id=t3.binfo_borrowid
LEFT JOIN bypal_facilitator_trade t4 ON t3.binfo_borrowid=t4.trade_contractnumber
LEFT join bypal_repay_detail t5 on  t5.binfo_id= any(
SELECT borrow_id FROM bypal_borrow_info  WHERE borrow_custid=(
SELECT borrow_custid FROM bypal_borrow_info WHERE borrow_id=$borrow_id )) AND repay_date=1
LEFT join bypal_management_type t6 ON t2.man_type_id=t6.man_type_id
LEFT join bypal_borrow_adjustvalue t7 ON t1.borrow_level=t7.ad_id
LEFT JOIN bypal_facilitator_itype t8 ON t2.fac_separatetype=t8.trade_number
LEFT JOIN bypal_cust_info t9 ON t1.borrow_custid=t9.cust_id
LEFT JOIN bypal_cust_loantype  t10 on t1.borrow_applyid=t10.lt_id
LEFT JOIN bypal_repay_method t11 ON t1.borrow_repayid=t11.repay_id
LEFT JOIN bypal_balance_invest t12 ON t1.borrow_custid=t12.cust_id
WHERE t1.borrow_id=$borrow_id";
        $Model = new \Think\Model();
        return $Model->query($sql);

    }

}